/**
 * 
 */
package com.clh.dao.impl;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.clh.dao.PayDao;
import com.clh.entity.Pay;
import com.clh.entity.PayType;
import com.clh.util.DBUtil;

/**
 * @Description: TODO
 * @author 陈志艺
 * @date 2022年5月31日 下午3:58:52
 * @version v1.0
 */
public class PayDaoImpl implements PayDao {
	@Override
	public List<Pay> findByPageInNoPay(String start, String end, String payTypeId, String roomNo,
			int pageNum, int pageSize) {
		List<Pay> list = new ArrayList<Pay>();
		StringBuilder sql = new StringBuilder();
		sql.append("select").append(" A.pay_no,").append(" A.room_no,").append(" A.pay_start_time,")
				.append(" A.pay_end_time,").append(" A.pay_type,").append(" B.type_name,").append(" A.pay_money,")
				.append(" A.pay_time,").append(" A.pay_status").append(" from pay A ").append(" inner join pay_type B ")
				.append(" on A.pay_type = B.type_id");
		// 动态sql
		if (true) {
			sql.append(" where 1=1 and pay_status=0 ");
		}
		if(payTypeId!=null&&!"".equals(payTypeId)) {
			sql.append(" and pay_type = '" + payTypeId + "'");
		}
		System.out.println("DaoImpl"+roomNo);
		// 根据参数情况下拼接不同的sql语句
		if (start != null && !"".equals(start)) {
			sql.append(" and date(pay_start_time)>='" + start + "'");
		}
		if (end != null && !"".equals(end)) {
			sql.append(" and date(pay_start_time)<='" + end + "'");
		}
		if (roomNo != null && !"".equals(roomNo)) {
			sql.append(" and room_no = '" + roomNo + "'");
		}
		// 分页功能
		sql.append(" limit " + (pageNum - 1) * pageSize + "," + pageSize);// limit 0,2
		System.out.println(sql.toString());
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				// 获取每一列数据
				String payNo = rs.getString("pay_no");
				 roomNo = rs.getString("room_no");
				String payStartTime = rs.getString("pay_start_time");
				String payEndTime = rs.getString("pay_end_time");
				Integer typeId = rs.getInt("pay_type");
				BigDecimal payMoney = rs.getBigDecimal("pay_money");
				Integer payStatus = rs.getInt("pay_status");
				String payTime = rs.getString("pay_time");
				String payName = rs.getString("type_name");// 商品类型名

				// 构建商品对象
				Pay pay1 = new Pay();
				pay1.setPayNo(payNo);
				pay1.setRoomNo(roomNo);
				pay1.setPayStartTime(payStartTime);
				pay1.setPayEndTime(payEndTime);
				pay1.setPayTypeId(typeId);
				pay1.setPayMoney(payMoney);
				pay1.setPayTime(payTime);
				pay1.setPayStatus(payStatus);

				PayType payType = new PayType();
				payType.setTypeId(typeId);
				payType.setTypeName(payName);

				// 将对象储存到集合中
				pay1.setPayType(payType);
				list.add(pay1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public List<Pay> findByPage(String start, String end,String roomNo, int pageNum, int pageSize) {
		// 动态sql，可以根据参数情况，拼接不同的sql语句
		// 情况1，没有房间号参数，查询所有
		// 情况2，房间号，根据房间号查询
		List<Pay> list = new ArrayList<Pay>();
		StringBuilder sql = new StringBuilder();
		sql.append("select").append(" A.pay_no,").append(" A.room_no,").append(" A.pay_start_time,")
				.append(" A.pay_end_time,").append(" A.pay_type,").append(" B.type_name,").append(" A.pay_money,")
				.append(" A.pay_time,").append(" A.pay_status").append(" from pay A ").append(" inner join pay_type B ")
				.append(" on A.pay_type = B.type_id");
		// 动态sql
		if (true) {
			sql.append(" where 1=1");
		}
		System.out.println("DaoImpl"+roomNo);
		// 根据参数情况下拼接不同的sql语句
		if (start != null && !"".equals(start)) {
			sql.append(" and date(pay_start_time)>='" + start + "'");
		}
		if (end != null && !"".equals(end)) {
			sql.append(" and date(pay_start_time)<='" + end + "'");
		}
		if (roomNo != null && !"".equals(roomNo)) {
			sql.append(" and room_no = '" + roomNo + "'");
		}
		// 分页功能
		sql.append(" limit " + (pageNum - 1) * pageSize + "," + pageSize);// limit 0,2
		System.out.println(sql.toString());
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				// 获取每一列数据
				String payNo = rs.getString("pay_no");
				 roomNo = rs.getString("room_no");
				String payStartTime = rs.getString("pay_start_time");
				String payEndTime = rs.getString("pay_end_time");
				Integer typeId = rs.getInt("pay_type");
				BigDecimal payMoney = rs.getBigDecimal("pay_money");
				Integer payStatus = rs.getInt("pay_status");
				String payTime = rs.getString("pay_time");
				String payName = rs.getString("type_name");// 商品类型名

				// 构建商品对象
				Pay pay1 = new Pay();
				pay1.setPayNo(payNo);
				pay1.setRoomNo(roomNo);
				pay1.setPayStartTime(payStartTime);
				pay1.setPayEndTime(payEndTime);
				pay1.setPayTypeId(typeId);
				pay1.setPayMoney(payMoney);
				pay1.setPayTime(payTime);
				pay1.setPayStatus(payStatus);

				PayType payType = new PayType();
				payType.setTypeId(typeId);
				payType.setTypeName(payName);

				// 将对象储存到集合中
				pay1.setPayType(payType);
				list.add(pay1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int countByCondition(String start, String end,  String roomNo) {
		int n = 0;
		StringBuilder sql = new StringBuilder();
		sql.append("select").append("  count(*)").append("  from pay A  ").append("  inner join pay_type B  ")
				.append("  on A.pay_type =B.type_id");
		// 动态SQL
		if (true) {
			sql.append("  where 1=1 ");
		}
		if (start != null && !"".equals(start)) {
			sql.append(" and date(pay_start_time)>='" + start + "'");
		}
		if (end != null && !"".equals(end)) {
			sql.append(" and date(pay_start_time)<='" + end + "'");
		}
		// 根据参数情况拼接不同的SQL语句
		if (roomNo != null && !"".equals(roomNo)) {
			sql.append(" and room_no = '" +roomNo + "'");
		}
		System.out.println(sql.toString());
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				n = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return n;
	}

	@Override
	public List<Pay> findByCondition(Pay pay) {
		// 动态sql，可以根据参数情况，拼接不同的sql语句
		// 情况1，没有房间号参数，查询所有
		// 情况2，房间号，根据房间号查询
		List<Pay> list = new ArrayList<Pay>();
		StringBuilder sql = new StringBuilder();
		sql.append("select").append(" A.pay_no,").append(" A.room_no,").append(" A.pay_start_time,")
				.append(" A.pay_end_time,").append(" A.pay_type,").append(" B.type_name,").append(" A.pay_money,")
				.append(" A.pay_time,").append(" A.pay_status").append(" from pay A ").append(" inner join pay_type B ")
				.append(" on A.pay_type = B.type_id");
		// 动态sql
		if (true) {
			sql.append(" where 1=1");
		}
		// 根据参数情况下拼接不同的sql语句
		if (pay.getRoomNo() != null && !"".equals(pay.getRoomNo())) {
			sql.append(" and room_no = '" + pay.getRoomNo() + "'");
		}

		System.out.println(sql.toString());
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				// 获取每一列数据
				String payNo = rs.getString("pay_no");
				String roomNo = rs.getString("room_no");
				String payStartTime = rs.getString("pay_start_time");
				String payEndTime = rs.getString("pay_end_time");
				Integer typeId = rs.getInt("pay_type");
				BigDecimal payMoney = rs.getBigDecimal("pay_money");
				Integer payStatus = rs.getInt("pay_status");
				String payTime = rs.getString("pay_time");
				String payName = rs.getString("type_name");// 商品类型名

				// 构建商品对象
				Pay pay1 = new Pay();
				pay1.setPayNo(payNo);
				pay1.setRoomNo(roomNo);
				pay1.setPayStartTime(payStartTime);
				pay1.setPayEndTime(payEndTime);
				pay1.setPayTypeId(typeId);
				pay1.setPayMoney(payMoney);
				pay1.setPayTime(payTime);
				pay1.setPayStatus(payStatus);

				PayType payType = new PayType();
				payType.setTypeId(typeId);
				payType.setTypeName(payName);

				// 将对象储存到集合中
				pay1.setPayType(payType);
				list.add(pay1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Pay> find() {
		// 动态sql，可以根据参数情况，拼接不同的sql语句
		// 情况1，没有房间号参数，查询所有
		// 情况2，房间号，根据房间号查询
		List<Pay> list = new ArrayList<Pay>();
		StringBuilder sql = new StringBuilder();
		sql.append("select").append(" A.pay_no,").append(" A.room_no,").append(" A.pay_start_time,")
				.append(" A.pay_end_time,").append(" A.pay_type,").append(" B.type_name,").append(" A.pay_money,")
				.append(" A.pay_time,").append(" A.pay_status").append(" from pay A ").append(" inner join pay_type B ")
				.append(" on A.pay_type = B.type_id");
		// 动态sql
		if (true) {
			sql.append(" where 1=1");
		}

		System.out.println(sql.toString());
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				// 获取每一列数据
				String payNo = rs.getString("pay_no");
				String roomNo = rs.getString("room_no");
				String payStartTime = rs.getString("pay_start_time");
				String payEndTime = rs.getString("pay_end_time");
				Integer typeId = rs.getInt("pay_type");
				BigDecimal payMoney = rs.getBigDecimal("pay_money");
				Integer payStatus = rs.getInt("pay_status");
				String payTime = rs.getString("pay_time");
				String payName = rs.getString("type_name");// 商品类型名

				// 构建商品对象
				Pay pay1 = new Pay();
				pay1.setPayNo(payNo);
				pay1.setRoomNo(roomNo);
				pay1.setPayStartTime(payStartTime);
				pay1.setPayEndTime(payEndTime);
				pay1.setPayTypeId(typeId);
				pay1.setPayMoney(payMoney);
				pay1.setPayTime(payTime);
				pay1.setPayStatus(payStatus);

				PayType payType = new PayType();
				payType.setTypeId(typeId);
				payType.setTypeName(payName);

				// 将对象储存到集合中
				pay1.setPayType(payType);
				list.add(pay1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Pay findByNo(String payNo) {
		Pay pay = new Pay();
		StringBuilder sql = new StringBuilder();
		sql.append("select")
		.append(" A.pay_no,")
		.append(" A.room_no,")
		.append(" A.pay_start_time,")
		.append(" A.pay_end_time,")
		.append(" A.pay_type,")
		.append(" B.type_name,")
		.append(" A.pay_money,")
		.append(" A.pay_time,")
		.append(" A.pay_status")
		.append(" from pay A ")
		.append(" inner join pay_type B ")
		.append(" on A.pay_type = B.type_id")
		.append(" where 1=1")
		.append(" and A.pay_no=?");
		

		System.out.println(sql.toString());
		ResultSet rs = DBUtil.query(sql.toString(), payNo);
		try {
			while (rs.next()) {
				// 获取每一列数据
				String roomNo = rs.getString("room_no");
				String payStartTime = rs.getString("pay_start_time");
				String payEndTime = rs.getString("pay_end_time");
				Integer typeId = rs.getInt("pay_type");
				BigDecimal payMoney = rs.getBigDecimal("pay_money");
				Integer payStatus = rs.getInt("pay_status");
				String payTime = rs.getString("pay_time");
				String payName = rs.getString("type_name");// 商品类型名

				// 构建商品对象
				pay.setPayNo(payNo);
				pay.setRoomNo(roomNo);
				pay.setPayStartTime(payStartTime);
				pay.setPayEndTime(payEndTime);
				pay.setPayTypeId(typeId);
				pay.setPayMoney(payMoney);
				pay.setPayTime(payTime);
				pay.setPayStatus(payStatus);

				PayType payType = new PayType();
				payType.setTypeId(typeId);
				payType.setTypeName(payName);

				// 将对象储存到集合中
				pay.setPayType(payType);
				System.out.println(pay);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pay;
	}

	@Override
	public int insert(Pay pay) {
		// 1、insert sql
		StringBuffer sql = new StringBuffer();
		sql.append(
				" insert into pay(pay_no,room_no,pay_start_time,pay_end_time,pay_type,pay_money,pay_time,pay_status)")
				.append(" value(?,?,?,?,?,?,?,?)");
		// 调用帮助类实现添加操作
		int n = DBUtil.update(sql.toString(), pay.getPayNo(), pay.getRoomNo(), pay.getPayStartTime(),
				pay.getPayEndTime(), pay.getPayTypeId(), pay.getPayMoney(), pay.getPayTime(), pay.getPayStatus());
		return n;
	}

	@Override
	public int update(Pay pay) {
		return DBUtil.update(
				"update pay set room_no=?,pay_start_time=?,pay_end_time=?,pay_type=?,pay_money=?,pay_time=?,pay_status=? where pay_no=?",
				pay.getRoomNo(), pay.getPayStartTime(), pay.getPayEndTime(), pay.getPayTypeId(), pay.getPayMoney(),
				pay.getPayTime(), pay.getPayStatus(), pay.getPayNo());
	}

	@Override
	public int deleteByNo(String payNo) {
		int rs = DBUtil.update("DELETE FROM pay WHERE pay_no=?", payNo);
		return rs;
	}
	@Override
	public int countByConditionByType(String start, String end,String payTypeId, String roomNo) {
		int n = 0;
		StringBuilder sql = new StringBuilder();
		sql.append("select").append("  count(*)").append("  from pay A  ").append("  inner join pay_type B  ")
				.append("  on A.pay_type =B.type_id");
		// 动态SQL
		if (true) {
			sql.append(" where 1=1 and pay_status=0 ");
		}
		if(payTypeId!=null&&!"".equals(payTypeId)) {
			sql.append(" and pay_type = '" + payTypeId + "'");
		}
		if (start != null && !"".equals(start)) {
			sql.append(" and date(pay_start_time)>='" + start + "'");
		}
		if (end != null && !"".equals(end)) {
			sql.append(" and date(pay_start_time)<='" + end + "'");
		}
		// 根据参数情况拼接不同的SQL语句
		if (roomNo != null && !"".equals(roomNo)) {
			sql.append(" and room_no = '" +roomNo + "'");
		}
		System.out.println(sql.toString());
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				n = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return n;
	}
	
	@Override
	public boolean isExist(String repeatPay) {
		String sql = "select * from pay where pay_no like '"+repeatPay+"%'";
		ResultSet rs = DBUtil.query(sql);
		try {
			if(rs.next()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		
		return false;
	}
	@Override
	public boolean updatePayStatus(String payNo) {
		Date date = new Date();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置日期格式
		String now = df.format(date);
		String sql = "update pay set pay_status=1 ,pay_time=? where pay_no=?";
		int n = DBUtil.update(sql,now, payNo);
		if(n>0) {
			return true;
		}
		return false;
	}


	

}
